# 爬取每条最新的数据
import requests
import time
import pymysql
# 不确定占不占运行时间
import socket

# 连接数据库
print("连接数据库")
db = pymysql.connect(host='localhost', user='root', password='663666', port=3306, db='btu')
print("数据库连接成功")
cursor = db.cursor()
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36'
}
url = f'https://api.huobipro.com/market/history/kline?period=1min&size=2000&symbol=btcusdt&AccessKeyId=3290265c-208217e4-9f4199c1-mk0lklo0de'
print("开始爬取")
for i in range(999999999999999999):
    # timeout 等待连接的秒数
    time.sleep(20)
    response = requests.get(url, headers=headers, timeout=30)
    # 如果服务器在 10 秒内没有应答，将会引发一个异常（更精确地说，是在 timeout 秒内没有从基础套接字上接收到任何字节的数据时)。
    time.sleep(10)
    print(response.status_code)
    if response.json().get("status") == "ok":
        datas = response.json().get("data")
        for data in datas:
            # 查看数据
            print(data)
            id = data['id']
            # 利用localtime()函数将时间戳转化成localtime的格式
            time_local = time.localtime(id)
            # 利用strftime()函数重新格式化时间
            dt = time.strftime("%Y-%m-%d %H:%M", time_local)
            print(dt)
            # 成交量
            amount = data['amount']
            # 成交笔数
            count = data['count']
            # 开盘价
            opens = data['open']
            # 收盘价
            close = data['close']
            # 最低价
            low = data['low']
            # 最高价
            high = data['high']
            # 成交额
            vol = data['vol']
            # 增加了涨幅计算
            # 涨幅情况(计算形式: (收盘价-开盘价)/开盘价*100)=涨幅度)
            increase = '{:.2f} %'.format((close - opens) / opens * 100)
            # 涨幅
            print(increase)
            sqlname = "demo_btc"
            # 统计数据条数
            query = "select count(*) from {}".format(sqlname)
            # 插入
            insert_btc = ("INSERT INTO demo_btc(time, price, increase)" "VALUE(%s, %s, %s)")
            # 删除  第一条数据
            sqldel = "delete from demo_btc limit 1"
            cursor.execute(query)
            count_wm = cursor.fetchall()
            print(count_wm[0][0])
            try:
                if (count_wm[0][0]) >= 30:
                    cursor.execute(sqldel)
                    db.commit()
                    print('删除成功')
                    print(sqlname, "数据库表中共有", count_wm[0][0], "数据")
                    cursor.execute(insert_btc, (dt, low, increase))
                    db.commit()
                    print("插入1条新数据成功")
                else:
                    cursor.execute(insert_btc, (dt, low, increase))
                    db.commit()
                    print("新插入1条数据成功")
            except:
                print("插入/删除/更新数据失败")
                db.rollback()
            break
        print("操作成功")
    time.sleep(30)

    # f.close
